package com.cyy.srv

import com.cyy.model.Constants
import com.cyy.model.GmodelModel
import com.jfinal.kit.Kv
import com.jfinal.plugin.activerecord.generator.Generator
import com.jfinal.plugin.activerecord.generator.MetaBuilder
import com.jfinal.plugin.druid.DruidPlugin
import java.sql.Connection
import tornadofx.*
import java.sql.DriverManager
import java.sql.ResultSet
import javax.sql.DataSource
import com.jfinal.plugin.activerecord.ActiveRecordPlugin
import com.jfinal.plugin.activerecord.Db
import com.jfinal.template.source.FileSourceFactory
import com.jfinal.template.source.ISource
import com.jfinal.template.source.ISourceFactory
import java.io.File


class GenSrv : Controller() {

    val gmodel: GmodelModel by inject()

    lateinit var con: Connection
    lateinit var rs: ResultSet
    var al = ArrayList<String>() // database list

    /**
     * 测试mysql或者postgresql等数据库是否能够成功连接，对于sqlite，如数据库不存在，则会创建一个空的数据库
     * test wheather or not mysql, postgresql, sqlite can be connected
     * 同时设置dataSource、metaBuilder、dialect、metaData、jdbcUrl
     * @return Boolean 是否能成功连接数据库服务
     */
    fun getCon(): Boolean {
        var (a, b) = Constants.setJdbcUrlAndDriver(gmodel.dbtype.value, gmodel.host.value, gmodel.port.value, gmodel.dbname.value)
        gmodel.jdbcUrl.value = a
//        gmodel.driver.value=b
//        Class.forName(b)

        gmodel.driver.value = Constants.setDbDriver1(gmodel.dbtype.value)
        Class.forName(gmodel.driver.value)
        try {
            con = DriverManager.getConnection(a, gmodel.user.value, gmodel.pwd.value)
            return true
        } catch (e: Exception) {
            println(e)
            return false
        }
    }

    /**
     * get database list
     * @return ArrayList<String> 数据库名称列表
     */
    fun getDBs(): ArrayList<String> {
        //先清空，再添加获取的数据库列表，避免每次点击重复添加相同的数据库
        if (al.isNotEmpty()) {
            al.clear()
        }
        when (gmodel.dbtype.value) {
            "postgresql" -> {
                rs = con.metaData.connection.prepareStatement(Constants.getPgDbsSqlString).executeQuery()
                while (rs.next()) {
//                    println(rs.getString(1))
                    al.add(rs.getString(1))
                }
            }

            "mysql" -> {
                rs = con.metaData.catalogs
                while (rs.next()) {
                    al.add(rs.getString(Constants.getMysqlDbsSqlString))
                }
            }
            "sqlite" -> al.add(gmodel.dbname.value)
            "h2" -> al.add(gmodel.dbname.value)
        }
        if (gmodel.dbs.isNotEmpty()) {
            gmodel.dbs.removeAt(0)
        }
        gmodel.dbs.addAll(al)
        return al
    }

    //    获取数据源
    fun getDataSource(jdbcUrl: String): DataSource {
        val druidPlugin = DruidPlugin(jdbcUrl, gmodel.user.value, gmodel.pwd.value)
        gmodel.arp.value = ActiveRecordPlugin(druidPlugin)

        gmodel.arp.value.addSqlTemplate(FileSourceFactory().getSource("${File("").canonicalPath}\\doc\\sql", "all.sql", "utf8"))
        druidPlugin.start()
        gmodel.arp.value.start()
        return druidPlugin.getDataSource()
    }

    fun gen() {
        // 创建生成器
//        val generator = Generator(ds, bmpkg, bmpkgpath, mpkg, modelOutputDir)
        val generator = Generator(gmodel.dataSource.value, gmodel.bmpkg.value, gmodel.bmpkgpath.value, gmodel.mpkg.value, gmodel.modelOutputDir.value)

        // 配置是否生成备注
        generator.setGenerateRemarks(gmodel.remark.value)
//        generator.setBaseModelTemplate("projects/IdeaProjects/javafx/generator-sqlite/tpl/model_template.jf")
//        generator.setModelTemplate("projects/IdeaProjects/javafx/generator-sqlite/tpl/model_template.jf")
//        generator.setModelTemplate(gmodel.modelTemplate.value)
        generator.setModelTemplate("jfinal/model_template.jf")

        // 设置数据库方言
        generator.setDialect(gmodel.dialect.value)

        // 设置是否生成链式 setter 方法
        generator.setGenerateChainSetter(true)


        // 添加不需要生成的表名
        generator.addExcludedTable("data_dictionary", "data_dictionary_value",
                "file_uploaded", "sys_function", "sys_log", "sys_org",
                "sys_role", "sys_role_function", "sys_user", "sys_user_role")

        // 设置是否在 Model 中生成 dao 对象
        generator.setGenerateDaoInModel(true)

        // 设置是否生成字典文件
        generator.setGenerateDataDictionary(true)

        // 设置需要被移除的表名前缀用于生成modelName。例如表名 "osc_user"，移除前缀 "osc_"后生成的model名为 "User"而非 OscUser
        generator.setRemovedTableNamePrefixes("four100_")

        // 生成
        generator.generate()
    }

    /**
     * get all tables in selected databases for mysql
     * @return ArrayList<String> 选择的数据库中的表名称列表
     */
    fun getAllTables(): ArrayList<String> {
        gmodel.jdbcUrl.value = Constants.setJdbcUrl2(gmodel.dbtype.value, gmodel.host.value, gmodel.port.value, gmodel.dbname.value)
//        gmodel.dialect.value=Constants.setDialect(gmodel.dbtype.value)
        gmodel.dialect.value = Constants.setDialect1(gmodel.dbtype.value)
        gmodel.dataSource.value = getDataSource(gmodel.jdbcUrl.value)
        gmodel.metaBuilder.value = MetaBuilder(gmodel.dataSource.value)
        gmodel.metaBuilder.value.setDialect(gmodel.dialect.value)
        val tblList = ArrayList<String>()
        gmodel.getTablesSql.value = Constants.setTablesSql(gmodel.dbtype.value, gmodel.dbname.value)
        rs = gmodel.dataSource.value.connection.prepareStatement(gmodel.getTablesSql.value).executeQuery()

        while (rs.next()) {
            if (rs.getString(1).isNotEmpty()) {
                tblList.add(rs.getString(1))
            }
        }
        gmodel.tables.addAll(tblList)
        return tblList
    }


    /**
     * 当数据库切换时，得到所选择数据库中的所有表，并将新的jDbPro对象加入到engine的SharedObjectMap中
     */
    fun prapare() {
        // 停止之前的activeRecordPlugin
//        try {
//            gmodel.arp.value.stop()
//        } catch (e: Exception) {
//            println("activeRecordPlugin stop failed :${e}")
//        }
        if (gmodel.arp.value != null) {
            gmodel.arp.value.stop()
        }
        when (gmodel.dbtype.value) {
            "sqlite", "h2" -> {
                gmodel.jdbcUrl.value = Constants.setJdbcUrl1(gmodel.dbtype.value, gmodel.host.value, gmodel.port.value, gmodel.dbname.value)
            }
            "mysql", "postgresql", "oracle", "sqlserver" -> {
                gmodel.jdbcUrl.value = Constants.setJdbcUrl2(gmodel.dbtype.value, gmodel.host.value, gmodel.port.value, gmodel.dbname.value)
            }
        }

        gmodel.tables.clear()
        this.getAllTables()
        gmodel.tableCounts.value = "当前数据库中共有${gmodel.tables.size.toString()}张表"

//        Constants.addSharedObject(gmodel.engine.value, Kv.by("jDbPro", Db()))
    }
}